package com.turing.post.cardmanagement.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.cardmanagement.util.ActivateCard;
import com.turing.post.cardmanagement.util.ActivateCardDao;
import com.turing.post.cardmanagement.util.CardEventLog;
import com.turing.post.cardmanagement.util.CardEventLogDao;
import com.turing.post.cardmanagement.util.CardEventLogReport;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "cardmanagement.impl.CardEventLogDaoImpl")
@Remote
@Local
public class CardEventLogDaoImpl implements CardEventLogDao {

	@PersistenceContext
	protected EntityManager _em;

	@EJB(mappedName = "cardmanagement.impl.ActivateCardDaoImpl")
	private ActivateCardDao acDao;

	/**
	 * 插入CardEventLog记录
	 * 
	 * @param cel
	 * @throws PCPSApplicationException
	 */
	@Override
	public void insert(CardEventLog cel) throws PCPSApplicationException {
		GeneralDao.insert(_em, cel);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<CardEventLog> getCardEventLogList(PCPSWebInput webInput,
			String startTranDate, String endTranDate, String cardNo,
			BigInteger cardClassicId, String eventType, Integer page) {
		if (PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		if (!PCPSUtil.isEmpty(cardNo)) {
			ActivateCard card = acDao.getActivateCardByCardNoOrSeqNo(cardNo);
			if (card != null) {
				cardNo = card.getCardNo();
			}
		}
		String sql = "select * from CardEventLog where issueCompanyNo = ?1 ";
		sql += PCPSUtil.getSqlCond(webInput, webInput.getOperBranchNo(),
				"branchNo");
		sql += " and (?2 = '-1' or cardNo = ?2) ";
		sql += " and (?3 = -1 or cardClassicId = ?3) ";
		sql += " and (?6 = '-1' or eventType = ?6) ";
		sql += " and (tranTime >= to_date(?4,'yyyy-MM-dd hh24:mi:ss') and tranTime <= to_date(?5,'yyyy-MM-dd hh24:mi:ss')) ";
		sql += " order by tranTime desc ";
		if (startTranDate == null) {
			startTranDate = PCPSUtil.getPackedYMDString2(new Date())
					+ " 00:00:00";
		} else {
			startTranDate = startTranDate + " 00:00:00";
		}
		if (endTranDate == null) {
			endTranDate = PCPSUtil.getPackedYMDString2(new Date())
					+ " 23:59:59";
		} else {
			endTranDate = endTranDate + " 23:59:59";
		}
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		Query query = _em.createNativeQuery(sql, CardEventLog.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, PCPSUtil.getIssueCompanyNo(webInput));
		query.setParameter(2, PCPSUtil.isEmpty(cardNo) == true ? "-1" : cardNo);
		query.setParameter(3, cardClassicId == null ? -1 : cardClassicId);
		query.setParameter(4, startTranDate);
		query.setParameter(5, endTranDate);
		query.setParameter(6, PCPSUtil.isEmpty(eventType) == true ? "-1"
				: eventType);
		PCPSUtil.Log(sql);
		return query.getResultList();
	}

	@Override
	public BigInteger getCardEventLogCount(PCPSWebInput webInput,
			String startTranDate, String endTranDate, String cardNo,
			BigInteger cardClassicId, String eventType) {
		if (PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return BigInteger.ZERO;
		}
		if (!PCPSUtil.isEmpty(cardNo)) {
			ActivateCard card = acDao.getActivateCardByCardNoOrSeqNo(cardNo);
			if (card != null) {
				cardNo = card.getCardNo();
			}
		}
		String sql = "select count(*) from CardEventLog where issueCompanyNo = ?1 ";
		sql += PCPSUtil.getSqlCond(webInput, webInput.getOperBranchNo(),
				"branchNo");
		sql += " and (?2 = '-1' or cardNo = ?2) ";
		sql += " and (?3 = -1 or cardClassicId = ?3) ";
		sql += " and (?6 = '-1' or eventType = ?6) ";
		sql += " and (tranTime >= to_date(?4,'yyyy-MM-dd hh24:mi:ss') and tranTime <= to_date(?5,'yyyy-MM-dd hh24:mi:ss')) ";
		if (startTranDate == null) {
			startTranDate = PCPSUtil.getPackedYMDString2(new Date())
					+ " 00:00:00";
		} else {
			startTranDate = startTranDate + " 00:00:00";
		}
		if (endTranDate == null) {
			endTranDate = PCPSUtil.getPackedYMDString2(new Date())
					+ " 23:59:59";
		} else {
			endTranDate = endTranDate + " 23:59:59";
		}
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, PCPSUtil.getIssueCompanyNo(webInput));
		query.setParameter(2, PCPSUtil.isEmpty(cardNo) == true ? "-1" : cardNo);
		query.setParameter(3, cardClassicId == null ? -1 : cardClassicId);
		query.setParameter(4, startTranDate);
		query.setParameter(5, endTranDate);
		query.setParameter(6, PCPSUtil.isEmpty(eventType) == true ? "-1"
				: eventType);
		PCPSUtil.Log(sql);
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public CardEventLog getCardEventLog(BigInteger cardEventId) {
		return _em.find(CardEventLog.class, cardEventId);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardEventLogReportCountByCondition(
			PCPSWebInput webInput, String branchNo, String startDate,
			String endDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String sqlCond = PCPSUtil.getSqlCond(webInput, branchNo, "c.BranchNo");
		String sql = "select c.branchno,c.EventType,sum(c.CashFee)/100,sum(c.cardfee)/100,sum(c.factfee)/100,count(*) from CardEventLog c where 1=1"
				+ sqlCond
				+ " and c.TranDate >=to_date(?1, 'yyyy-mm-dd') and c.TranDate <=to_date(?2, 'yyyy-mm-dd') and c.factfee>0 group by c.branchno,c.EventType order by c.branchno,c.EventType";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<CardEventLogReport> getCardEventLogReportListByCondition(
			PCPSWebInput webInput, String branchNo, String startDate,
			String endDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String sqlCond = PCPSUtil.getSqlCond(webInput, branchNo, "t.BranchNo");
		String sql = "select a.batchno,a.cardseqno,t.cardno,a.salebranchno,s.salebranchname,"
				+ "a.initialbalance initialbalance,t.balance balance,t.feeratio,"
				+ "decode(t.factfee, null, 0, t.factfee) fee,"
				+ "decode(t.cardFee, null, 0, t.cardFee) cardFee,"
				+ "decode(t.cashFee, null, 0, t.cashFee) cashFee,"
				+ "decode(t.discountFee, null, 0, t.discountFee) discountFee,"
				+ "t.eventtype,to_char(t.trantime,'yyyy-mm-dd hh24:mi:ss') trantime,u.name"
				+ " from CardEventLog t, activatecard a, salebranch s, sysopuser u"
				+ " where t.cardno = a.cardno and a.salebranchno = s.salebranchno"
				+ " and t.operid = u.operid"
				+ sqlCond
				+ " and t.TranDate >=to_date(?1, 'yyyy-mm-dd')"
				+ " and t.TranDate <=to_date(?2, 'yyyy-mm-dd')"
				+ " order by trantime desc";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		List<Object[]> list = query.getResultList();
		// 类型转换
		CardEventLogReport celr = null;
		List<CardEventLogReport> resultlist = new ArrayList<CardEventLogReport>();
		for (Object[] objArr : list) {
			celr = new CardEventLogReport();
			celr.setBatchNo(objArr[0].toString());
			celr.setCardSeqNo(objArr[1].toString());
			celr.setCardNo(objArr[2].toString());
			celr.setSaleBranchNo(objArr[3].toString());
			celr.setSaleBranchName(objArr[4].toString());
			celr.setInitialBalance(new BigInteger(objArr[5].toString()));
			celr.setBalance(new BigInteger(objArr[6].toString()));
			celr.setFeeratio(new BigInteger(objArr[7] == null ? "0" : objArr[7]
					.toString()));
			celr.setFee(new BigInteger(objArr[8].toString()));
			celr.setCardFee(new BigInteger(objArr[9].toString()));
			celr.setCashFee(new BigInteger(objArr[10].toString()));
			celr.setDiscountFee(new BigInteger(objArr[11].toString()));
			celr.setEventType(CardEventLogReport.getEventTypeMp().get(
					objArr[12].toString()));
			celr.setTime(objArr[13].toString());
			celr.setName(objArr[14].toString());
			resultlist.add(celr);
		}
		return resultlist;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getBranchDaySettByBranchNoAndTranDate(
			String issueCompanyNo, String branchNo, Date tranDate) {
		/*
		 * 20–挂失换卡 21-换发新卡 22-正式挂失 40–变更卡种类 41–登记或变更实名信息 99-调整有效期 30-补磁
		 * 11-强行解除密码错误锁定 03-冻结 13-解除冻结 23-临时挂失 24-解挂失 50-手工锁定 51-解除手工锁定
		 * 97-密码重置(2013-1-25) 98-修改密码
		 * 
		 */
		String sql = "select BranchNo, EventType, count(*), sum(Fee), sum(DiscountFee), sum(FactFee), sum(CardFee), sum(CashFee) from CardEventLog "
				+ "where IssueCompanyNo = ?1 and BranchNo is NOT NULL AND (BranchNo = ?2 or ?2 is NULL) and TranDate = ?3 "
				+ "and EventType in ('03', '11', '13', '20','21','22', '23', '24', '30', '40','41','50', '51', '97', '98', '99') "
				+ "group by BranchNo, EventType";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		if (PCPSUtil.isEmpty(branchNo))
			query.setParameter(2, null);
		else
			query.setParameter(2, branchNo);
		query.setParameter(3, tranDate);
		List<Object[]> objlist = query.getResultList();

		return objlist;
	}

	@Override
	public void updateCardEventLog(CardEventLog cel)
			throws PCPSApplicationException {
		GeneralDao.update(_em, cel);
	}

	@Override
	public BigInteger getCardEventFactFeeBySaleBrchNos(String saleBrchNos,
			String startDate, String endDate) {
		String sql = "select nvl(sum(t.factFee),0) from cardeventlog t"
				+ " where t.branchno in (" + saleBrchNos + ")"
				+ " and t.trandate >=to_date(?1,'yyyy-MM-dd')"
				+ " and t.trandate <=to_date(?2,'yyyy-MM-dd')";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		return new BigInteger(query.getSingleResult().toString());
	}
}